﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace SqlCeDriver
{
	public class SqlCEOptionInterfaceImplementations : DbViewDriver.ISchemaInfo, DbViewDriver.ILookup
	{
		SqlCeDataSource datasource;
		SqlCEDataAccessor dataAccessor;
		String connectionString;
		private List<DbView.FieldInfo> fields {get; set;}
		private string table="";

		public SqlCEOptionInterfaceImplementations(SqlCEDataAccessor accessor, SqlCeDataSource ds)
		{
			this.datasource = ds;
			this.dataAccessor = accessor;
		}
		//////////////////////////////////////////////////////
		// 
		public string ConnectionString
		{
			get
			{
				return datasource.ConnectionString;
			}
		}

		public DbView.FieldInfo[] GetSchema(string tableName)
		{
			if (this.fields == null || tableName != table)  // cache not valid condition
			{
				SqlTableSchema schema = Helper.MakeSchemaFor(tableName, this.dataAccessor, this.connectionString);
				fields = new List<DbView.FieldInfo>();
				for (int idx = 0; idx < schema.Count; ++idx)
					fields.Add(schema[idx]);
			}
			return this.fields.ToArray();
		}
		////////////////////////////////////////////////////////////////////////////////
		// Hacky hack hack for execution speed on large databases.
		// given an array of tables return a map of table to arrays of the field info that matters.
		//
		public Dictionary<string, DbView.FieldInfo[]> GetBasicSchemas(string[] tableNames)
		{
			Dictionary<string, DbView.FieldInfo[]> map = new Dictionary<string,DbView.FieldInfo[]>();
			string search = string.Join("','", tableNames);
			string template = @"
select  cols.TABLE_NAME, 
        cols.COLUMN_NAME as ColumnName,
		cols.DATA_TYPE as [Type],
		convert(nvarchar(20), case when cols.DATA_TYPE in ('numeric')
									then cols.NUMERIC_PRECISION 
									else cols.CHARACTER_MAXIMUM_LENGTH end) as [Length],
		case when cols.DATA_TYPE in ('numeric')
				then cols.NUMERIC_SCALE 
				else NULL end as [Dp],
		case when i.is_non_pk_index = 0 then 'YES' else NULL end as [Index?],
		REPLACE(REPLACE('(%s,%i)','%s',AUTOINC_SEED),'%i',AUTOINC_INCREMENT) as [Auto?],
		case when i.is_primary_key = 1 then 'YES' else NULL end as [PKey?],
		case when fk.is_fk = 1 then 'YES' else NULL end as [FKey?],
		cols.IS_NULLABLE as [Nulls?],
		cols.COLUMN_DEFAULT as [Default]
  from INFORMATION_SCHEMA.COLUMNS cols
left join (select i.TABLE_NAME, i.COLUMN_NAME, 
					max(cast(PRIMARY_KEY as tinyint)) as is_primary_key, 
					min(cast(PRIMARY_KEY as tinyint)) as is_non_pk_index
			  from INFORMATION_SCHEMA.COLUMNS cols
			  join INFORMATION_SCHEMA.INDEXES i on i.TABLE_NAME = cols.TABLE_NAME and i.COLUMN_NAME = cols.COLUMN_NAME
			group by i.TABLE_NAME, i.COLUMN_NAME 
		 ) i on i.TABLE_NAME = cols.TABLE_NAME and i.COLUMN_NAME = cols.COLUMN_NAME
left join (select distinct u.TABLE_NAME, u.COLUMN_NAME, 1 as is_FK
			  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
			  join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u on u.CONSTRAINT_NAME = c.CONSTRAINT_NAME
			 where c.CONSTRAINT_TYPE = 'FOREIGN KEY'
		   ) fk on fk.TABLE_NAME = cols.TABLE_NAME and fk.COLUMN_NAME = cols.COLUMN_NAME
 where cols.TABLE_NAME in ('{0}')
 order by cols.ORDINAL_POSITION
";
			string sql = string.Format(template, search);
			DataTable dt = new Helper(this.dataAccessor).GenericQuery(this.ConnectionString, sql, "FK");
			// collects data for a table
			string currentTable = ""; 
			List<DbView.FieldInfo> schema = new List<DbView.FieldInfo>();
			foreach (DataRow dr in dt.Rows)
			{
				String tableName = dr[0].ToString();
				String name = dr[1].ToString();
				String sqlType = dr[2].ToString();
				String leng = dr[3].ToString();
				String dp = dr[4].ToString();
				String pk = dr[5].ToString();
				String nulls = dr[6].ToString(); nulls = (nulls.Length == 0) ? "Yes" : nulls; // Special. For null column blank means Yes
				//
				int len = (leng.ToUpper() == "MAX") ? -1 :
						  (leng.Length == 0) ? 0 : // 2000 driver
						   Convert.ToInt32(leng);
				DbView.FieldInfo fi = new DbView.FieldInfo(name, sqlType, len);
				fi.IsPKey = this.TfFromData(pk);
				if (sqlType.ToUpper() == "NCHAR" || sqlType.ToUpper() == "NVARCHAR")
					fi.IsUnicode = true;
				fi.AllowsNulls = this.TfFromData(nulls);
				fi.Dp = (dp.Length == 0) ? 0 : Convert.ToInt32(dp);
				
				// turnover test
				if (tableName != currentTable)
				{
					// update if something to update. T except for first row
					if (!string.IsNullOrEmpty(currentTable))
					{
						map.Add(currentTable, schema.ToArray());
						schema.Clear();
					}
					currentTable = tableName;
				}
				schema.Add(fi);
			}
			// the last one is left to do.
			if (!string.IsNullOrEmpty(currentTable))
				map.Add(currentTable, schema.ToArray());
			return map;
		}

		String DataFromCell(DataRow dr, String columnName)
		{
			if (!dr.Table.Columns.Contains(columnName)) return "";
			return dr[columnName].ToString();
		}

		bool TfFromData(String data)
		{
			if (data.Length == 0) return false;  // empty means no
			char c = data[0];
			if (c == 'Y' || c == 'y' || c == 'T' || c == 't') return true;
			if (c == '1') return true;
			if (c == '0') return true;
			// Unless N or No then true
			if ((c == 'N' || c == 'n') && data.Length <= 2) return false;
			// handle other cases as they arise
			return true;
		}
		//////////////////////////////////////////////////////////////////

		public string[] GetTables()
		{
			// there is no filter
			string tableNameFilter = "";
			// get the short-form template for tables as this just needs names

			string template = SqlCeTemplates.Templates.FetchTemplate(3, DbView.Commands.Cmnd.Tables);
			string sql = SqlCeTemplates.Templates.ProcessFilterClause(template, tableNameFilter, this.datasource.IsCaseSensitive);
			DataTable dt = new Helper(this.dataAccessor).GenericQuery(ConnectionString, sql, "TABLES");
			List<string> tables = new List<string>();
			foreach (DataRow dr in dt.Rows)
			{
				tables.Add(dr[0].ToString());
			}
			return tables.ToArray();
		}

		public System.Data.DataTable GetFkInfo()
		{
			// get the standard FK template as it will do. (Cunning/oblique design!)
			string template = SqlCeTemplates.Templates.FetchTemplate(this.datasource.SqlVersion, DbView.Commands.Cmnd.ForeignKeys);
			// have to resolve the fancy lookup text
			string sql = SqlCeTemplates.Templates.ProcessFilterClause(template, "", this.datasource.IsCaseSensitive);
			DataTable dt = new Helper(this.dataAccessor).GenericQuery(ConnectionString, sql, "FK");
			return dt;
		}

		//////////////////////////////////////////////////////
		// 
		public DbViewDriver.ISchemaInfo GetSchemaInfo()
		{
			return this as DbViewDriver.ISchemaInfo;
		}

		public string BuildLookupCommand(string pkTable, string[] pkFields, string keyField, string lookupData)
		{
			string template = @"select [{0}] from {1} where {2} in ({3})";
			string fieldList = string.Join("],[",pkFields);
			string command = string.Format(template, fieldList, Helper.SafeTableName(pkTable), Helper.SafeSqlField(keyField), lookupData);
			// order by clause
			if (lookupData.Contains(",") )
			{
				string[] items = lookupData.Split(',');
				if (items.Length < 40)  // magic number. OrderBy threshold. More than this its not worth bothering with.
				{
					// but don't kill the sql engine
					string clause = "";
					for (int idx = 0; idx < items.Length; ++idx)
					{
						clause += string.Format("when {0} then {1} ", items[idx], idx);
					}
					string orderBy = string.Format(" order by case {0} {1} end", Helper.SafeSqlField(keyField), clause);
					command += orderBy;
				}
			}
			return command;
		}
	}
}
